package android.theHead;

import org.json.JSONArray;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class HeadDBAdapter {
	private static final String DATABASE_NAME = "tempData.db";
	private static final String DATABASE_TABLE = "mainTable";
	private static final int DATABASE_VERSION = 6;
	
	// The index (key) column name for use in where clauses.
	public static final String KEY_ID = "id";
	public static final int ID_COLUMN = 0;
	
	// The name and column index of each column in your database.
	public static final String KEY_UTCTIME = "utctime";
	public static final int TIME_COLUMN = 1;
	
	public static final String KEY_LONG = "long";
	public static final int LONG_COLUMN = 2;
	
	public static final String KEY_LAT = "lat";
	public static final int LAT_COLUMN = 3;
	
	public static final String KEY_TEMPO = "tempo";
	public static final int TEMPO_COLUMN = 4;
	
	public static final String KEY_SPEED = "speed";
	public static final int SPEED_COLUMN = 5;
	
	public static final String KEY_BEARING = "bearing";
	public static final int BEARING_COLUMN = 6;
	
	public static final String KEY_DISTANCE = "distance";
	public static final int DISTANCE_COLUMN = 7;
	
	public static final String KEY_ALTITUDE = "altitude";
	public static final int ALTITUDE_COLUMN = 8;
	
	public static final String KEY_ACCURACY = "accuracy";
	public static final int ACCURACY_COLUMN = 9;
	
	public static final String KEY_SENT="sent";
	public static final int SENT_COLUMN = 10;
	
	// SQL Statement to create a new database.
	private static final String DATABASE_CREATE = "create table " +
		DATABASE_TABLE + " (" + KEY_ID +
		" integer primary key autoincrement, " +
		KEY_UTCTIME + " INTEGER, " +
		KEY_LONG + " REAL, " +
		KEY_LAT + " REAL, " +
		KEY_TEMPO + " REAL, " +
		KEY_SPEED + " REAL, " +
		KEY_BEARING + " REAL, " +
		KEY_DISTANCE + " REAL, " +
		KEY_ALTITUDE + " REAL, " +
		KEY_ACCURACY + " REAL, " +
		KEY_SENT + " INTEGER" +
		");";
	
	// Variable to hold the database instance
	public SQLiteDatabase db;
	// Context of the application using the database.
	private final Context context;
	// Database open/upgrade helper
	private HeadDbHelper dbHelper;
	
	private int lastKey = -1;
	
	public HeadDBAdapter(Context _context) {
		this.context = _context;
		this.dbHelper = new HeadDbHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	public HeadDBAdapter open() throws SQLException {
		try {
			this.db = dbHelper.getWritableDatabase();
		} catch (SQLiteException ex) {
			this.db = dbHelper.getReadableDatabase();
		}
		return this;
	}
	
	public void close() {
		this.db.close();
	}
	
	
	/**
	 * Insert entry.
	 *
	 * @param entry the entry to insert into the data base
	 * @return the index of the inserted entry as long
	 */
	public long insertEntry(HeadReadings entry) {
		// Create a new row of values to insert.
		ContentValues newValues = new ContentValues();
		// Assign values for each row.
		newValues.put(KEY_UTCTIME, entry.utcTime);
		newValues.put(KEY_LONG, entry.longitude);
		newValues.put(KEY_LAT, entry.latitude);
		newValues.put(KEY_TEMPO, entry.tempo);
		newValues.put(KEY_SPEED, entry.speed);
		newValues.put(KEY_BEARING, entry.bearing);
		newValues.put(KEY_DISTANCE, entry.distance);
		newValues.put(KEY_DISTANCE, entry.distance);
		newValues.put(KEY_SENT, 0);
		
		Log.i("DB","start entering values");
		long result = this.db.insert(DATABASE_TABLE, null, newValues);
		Log.i("DB","ready with entering");
		return result;
	}
	
	public boolean removeEntry(long _rowIndex) {
		return this.db.delete(DATABASE_TABLE, KEY_ID + "=" + _rowIndex, null) > 0;
	}
	
	public Cursor getAllEntries () {
		return this.db.query(DATABASE_TABLE, new String[] {KEY_ID, KEY_UTCTIME, KEY_LONG, KEY_LAT, KEY_TEMPO, KEY_SPEED, KEY_BEARING, KEY_DISTANCE}, null, null, null, null, null);
	}

	// returns last key
	public int getLastKey(){
		return this.lastKey;
	}
	
	public JSONArray getUnsentEntries(){
		HeadReadings entry;
		JSONArray jArray = new JSONArray();
		Cursor c = this.db.rawQuery("Select * from " + DATABASE_TABLE 	+ " where " + KEY_SENT + "=0 order by " + KEY_ID + " asc", null);
		
		if (c.getCount()==0){
			c.close();
			return jArray;
		}

		while (c.moveToNext()){
			entry = new HeadReadings(c);
			
	    	jArray.put(entry.getJSON());
		}
		
		c.moveToLast();
		this.lastKey = c.getInt(ID_COLUMN);
		c.close();
		return jArray;
	}
	
	

	/**
	 * Mark all entries with a key smaller than or equal to a give key as sent (column sent = 1)
	 *
	 * @param key the key
	 */
	public void markUnsetSent(int key){
		this.db.execSQL("Update " + DATABASE_TABLE + " set " + KEY_SENT + " = 1 where " + KEY_SENT + " = 0 AND " + KEY_ID + " <= " + key);
		Log.i("markUnsentSent", "all unsent entries marked as sent");
	}
	
	
//	public void getEntry(long _rowIndex) {
//		// Return all columns for rows where column 3 equals a set value
//		// and the rows are ordered by column 5.
//		String where = KEY_ID + "=" + _rowIndex;
//		String order = KEY_ID;
//		Cursor myResult = this.db.query(DATABASE_TABLE, null, where, null, null, null, null);
//		
//		// TODO: Return a cursor to a row from the database and
//		// use the values to populate an instance of MyObject
////		return objectInstance;
//	}
	
//	public getNumberOfEntries(){
//		this.db.
//	}
	
	
	
	/**
	 * Gets the last entry.
	 *
	 * @return the last entry
	 */
	public HeadReadings getLastEntry(){
		Cursor c = db.rawQuery("select * from " + DATABASE_TABLE + " order by " + KEY_ID + " desc limit 1", null);
		c.moveToLast();
		if (c.getPosition() >= 0){
			HeadReadings entry = new HeadReadings(c);
			c.close();
			return entry;
    	} else {
    		Log.i("getLastEntry", "cursor.getPosition < 0");
    		c.close();
    		return null;
    	}
		
	}
	
//	public boolean updateEntry(long _rowIndex) {
//		// Create a new row of values to insert.
//		ContentValues newValues = new ContentValues();
//		// Assign values for each row.
//		newValues.put(KEY_UTCTIME, 1.0);
//		newValues.put(KEY_LONG, 2.0);
//		newValues.put(KEY_LAT, 3.0);
//		newValues.put(KEY_TEMPO, 4.0);
//		newValues.put(KEY_SPEED, 5.0);
//		newValues.put(KEY_BEARING, 6.0);
//		newValues.put(KEY_DISTANCE, 7.0);
//		
//		String where = KEY_ID + "=" + _rowIndex;
//
//		this.db.update(DATABASE_TABLE, newValues, where, null);
//		// TODO: Create a new ContentValues based on the new object
//		// and use it to update a row in the database.
//		return true;
//	}
	
	private static class HeadDbHelper extends SQLiteOpenHelper {
		
		public HeadDbHelper(Context context, String name, CursorFactory factory, int version) {
			super(context, name, factory, version);
		}
		
		// Called when no database exists in disk and the helper class needs
		// to create a new one.
		@Override
		public void onCreate(SQLiteDatabase _db) {
			_db.execSQL(DATABASE_CREATE);
		}
		
		// Called when there is a database version mismatch meaning that the version
		// of the database on disk needs to be upgraded to the current version.
		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
			// Log the version upgrade.
			Log.w("TaskDBAdapter", "Upgrading from version " + _oldVersion + " to " + _newVersion + ", which will destroy all old data");

			// Upgrade the existing database to conform to the new version. Multiple
			// previous versions can be handled by comparing _oldVersion and _newVersion
			// values.
		
			// The simplest case is to drop the old table and create a new one.
			_db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
		
			// Create a new one.
			onCreate(_db);
		}
	}
}